﻿using System;
using System.Data;
using System.Data.Common;

/// <summary>
/// Wraps department details data
/// </summary>
public struct DepartmentDetails
{
	public string Name;
	public string Description;
}

/// <summary>
/// Wraps category details data
/// </summary>
public struct CategoryDetails
{
	public int DepartmentId;
	public string Name;
	public string Description;
}

/// <summary>
/// Wraps product details data
/// </summary>
public struct ProductDetails
{
	public int ProductID;
	public int UserID;
	public int CategoryID;
	public string Name;
	public string Description;
	public decimal Price;
	public string Thumbnail;
	public string Image;
	public DateTime CreateDate;
	public DateTime ModifyDate;
	public bool Status;
}

/// <summary>
/// Product catalog business tier component
/// </summary>
public static class EF_CatalogAccess
{
	static EF_CatalogAccess()
	{
		//
		// TODO: Add constructor logic here
		//
	}

	// Get category details
	public static CategoryDetails GetCategoryDetails(string categoryId)
	{
		// get a configured DbCommand object
		DbCommand comm = GenericDataAccess.CreateCommand();
		// set the stored procedure name
		comm.CommandText = "EF_CatalogGetCategoryDetails";
		// create a new parameter
		DbParameter param = comm.CreateParameter();
		param.ParameterName = "@CategoryID";
		param.Value = categoryId;
		param.DbType = DbType.Int32;
		comm.Parameters.Add(param);

		// execute the stored procedure
		DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
		// wrap retrieved data into a CategoryDetails object
		CategoryDetails details = new CategoryDetails();
		if (table.Rows.Count > 0)
		{
			//details.DepartmentId = Int32.Parse(table.Rows[0]["DepartmentID"].ToString());
			details.Name = table.Rows[0]["Name"].ToString();
			details.Description = table.Rows[0]["Description"].ToString();
		}
		// return department details
		return details;
	}

	// Get product details
	public static ProductDetails GetProductDetails(string productId)
	{
		// get a configured DbCommand object
		DbCommand comm = GenericDataAccess.CreateCommand();
		// set the stored procedure name
		comm.CommandText = "EF_CatalogGetProductDetails";
		// create a new parameter
		DbParameter param = comm.CreateParameter();
		param.ParameterName = "@ProductID";
		param.Value = productId;
		param.DbType = DbType.Int32;
		comm.Parameters.Add(param);

		// execute the stored procedure
		DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
		// wrap retrieved data into a ProductDetails object
		ProductDetails details = new ProductDetails();
		if (table.Rows.Count > 0)
		{
			// get the first table row
			DataRow dr = table.Rows[0];
			// get product details
			details.ProductID = int.Parse(productId);
			details.Name = dr["Name"].ToString();
			details.Description = dr["Description"].ToString();
			details.Price = Decimal.Parse(dr["Price"].ToString());
			details.Thumbnail = dr["Thumbnail"].ToString();
			details.Image = dr["Image"].ToString();
			//details.CreateDate = DateTime.Parse(dr["CreateDate"].ToString());
			//details.ModifyDate = DateTime.Parse(dr["ModifyDate"].ToString());
			details.Status = bool.Parse(dr["Status"].ToString());
		}
		// return department details
		return details;
	}

	// retrieve the list of products in a category
	public static DataTable GetProductsInCategory
	(string categoryId, string pageNumber, out int howManyPages)
	{
		// get a configured DbCommand object
		DbCommand comm = GenericDataAccess.CreateCommand();
		// set the stored procedure name
		comm.CommandText = "EF_CatalogGetProductsInCategory";
		// create a new parameter
		DbParameter param = comm.CreateParameter();
		param.ParameterName = "@CategoryID";
		param.Value = categoryId;
		param.DbType = DbType.Int32;
		comm.Parameters.Add(param);
		// create a new parameter
		param = comm.CreateParameter();
		param.ParameterName = "@DescriptionLength";
		param.Value = ElegantFoodConfiguration.ProductFrontDescriptionLength;
		param.DbType = DbType.Int32;
		comm.Parameters.Add(param);
		// create a new parameter
		param = comm.CreateParameter();
		param.ParameterName = "@PageNumber";

		param.Value = pageNumber;
		param.DbType = DbType.Int32;
		comm.Parameters.Add(param);
		// create a new parameter
		param = comm.CreateParameter();
		param.ParameterName = "@ProductsPerPage";
		param.Value = ElegantFoodConfiguration.ProductsFrontPerPage;
		param.DbType = DbType.Int32;
		comm.Parameters.Add(param);
		// create a new parameter
		param = comm.CreateParameter();
		param.ParameterName = "@HowManyProducts";
		param.Direction = ParameterDirection.Output;
		param.DbType = DbType.Int32;
		comm.Parameters.Add(param);
		// execute the stored procedure and save the results in a DataTable
		DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
		// calculate how many pages of products and set the out parameter
		int howManyProducts = Int32.Parse(comm.Parameters["@HowManyProducts"].Value.ToString());
		howManyPages = (int)Math.Ceiling((double)howManyProducts / (double)ElegantFoodConfiguration.ProductsFrontPerPage);
		// return the page of products
		return table;
	}
}